﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Linq;
using System.Data.Linq;

namespace ServerSideTest
{
	internal class SampleGenerator
	{
		private uint TagSeed;
		private uint StationSeed;
		private uint CasterSeed;

		public SampleGenerator()
		{
			TagSeed = 0;
			StationSeed = 0;
			CasterSeed = 0;
		}

		public string NextStation()
		{
			return string.Format("Station_{0}", ++StationSeed);
		}

		public string PeekStation()
		{
			return string.Format("Station_{0}", StationSeed);
		}

		public string NextCaster()
		{
			return string.Format("Caster_{0}", ++CasterSeed);
		}

		public string PeekCaster()
		{
			return string.Format("Caster_{0}", CasterSeed);
		}

		public string NextTag()
		{
			return string.Format("Tag_{0}",++TagSeed);
		}

		public string PeekTag()
		{
			return string.Format("Tag_{0}", TagSeed);
		}
	}


	internal class SqlDirectControl:IDisposable
	{
		public static readonly string ConStr = @"Data Source=THUNDERBOLT\SQLEXPRESS;Initial Catalog=NamaAlert;Integrated Security=True;Connection Timeout=3000";
		private SqlConnection con;
		private NamaAlertEntities entity;
		private uint MaxTagsCount_;

		private SqlCommand AddCmd, ModCmd, DelCmd;
		private SqlParameter sid, cstr, tag, tt, ft;

		public SqlDirectControl()
		{
			con = 
				new SqlConnection(ConStr);
			con.Open();

			entity = new NamaAlertEntities();

			using (SqlCommand cmd = con.CreateCommand())
			{
				cmd.CommandText = "SELECT dbo.ProcedureConfigure.Value FROM dbo.ProcedureConfigure WHERE Name='MaxTagsCount';";
				MaxTagsCount_ = uint.Parse((string)cmd.ExecuteScalar());
			}

			sid = new SqlParameter("@sid", System.Data.SqlDbType.NVarChar);
			cstr = new SqlParameter("@cstr", System.Data.SqlDbType.NVarChar);
			tag = new SqlParameter("@tag", System.Data.SqlDbType.NVarChar);
			tt = new SqlParameter("@tt", System.Data.SqlDbType.NVarChar);
			ft = new SqlParameter("@ft", System.Data.SqlDbType.NVarChar);

			AddCmd = con.CreateCommand();
			AddCmd.CommandText = "EXECUTE dbo.AddTag @sid,@cstr,@tag;";

			ModCmd = con.CreateCommand();
			ModCmd.CommandText = "EXECUTE dbo.ModifyTag @sid,@cstr,@ft,@tt;";

			DelCmd = con.CreateCommand();
			DelCmd.CommandText = "EXECUTE	dbo.DeteleTag @sid,@cstr,@tag;";
		}

		public void DeleteAllTable()
		{
			using (SqlCommand cmd = con.CreateCommand())
			{
				cmd.CommandText="DELETE FROM dbo.Bind;";
				cmd.ExecuteNonQuery();

				cmd.CommandText="DELETE FROM dbo.LiveInfo;";
				cmd.ExecuteNonQuery();

				cmd.CommandText="DELETE FROM dbo.Tags;";
				cmd.ExecuteNonQuery();
			}
		}

		public int Count(string TagValue)
		{
			var Ret = entity.Tags.Where(tmp => tmp.Value == TagValue)
				.Count();

			return Ret;
		}

		public int Count(string Station, string Caster)
		{
			var Ret = entity.LiveInfo.Where(tmp => tmp.Caster == Caster && tmp.StationId == Station)
				.Count();

			return Ret;
		}

		public int Count(string Station, string Caster, string TagValue)
		{
			int Ret = entity.TagInfo.Where(tmp => tmp.StationId == Station && tmp.Caster == Caster && tmp.TagValue == TagValue)
				.Count();

			return Ret;
		}

		public int GetLiveInfoCount()
		{
			int Ret = entity.LiveInfo.Count();
			return Ret;
		}

		public int GetTagsCount()
		{
			int Ret = entity.Tags.Count();
			return Ret;
		}

		public int GetBindCount()
		{
			int Ret = entity.Bind.Count();
			return Ret;
		}

		public IQueryable<string> GetTag(string Station, string Caster)
		{
			LiveInfo liveinfo = new LiveInfo();
			var Ret = entity.TagInfo.Where(tmp => tmp.StationId == Station && tmp.Caster == Caster)
				.Select(tmp => tmp.TagValue);

			return Ret;
		}

		public bool IsAllTableNoRecord()
		{
			var TagCnt = entity.TagInfo.Count();
			var LiveCnt = entity.LiveInfo.Count();
			var BindCnt = entity.Bind.Count();

			return TagCnt == 0 && LiveCnt == 0 && BindCnt == 0;
		}

		public void Add(string sid,string cstr,string tag)
		{
			ResetParameter(AddCmd, sid, cstr,tag);
			AddCmd.ExecuteNonQuery();
		}

		public void Modify(string sid, string cstr, string ft,string tt)
		{
			ResetParameter(ModCmd, sid, cstr, ft, tt);
			ModCmd.ExecuteNonQuery();
		}

		public void Delete(string sid, string cstr, string tag)
		{
			ResetParameter(DelCmd,sid, cstr, tag);
			DelCmd.ExecuteNonQuery();
		}

		private void ResetParameter(SqlCommand BindedCommand, string StationId, string Caster,string TagValue)
		{
			if (StationId != null)
			{
				this.sid.Value = StationId;
			}
			else
			{
				this.sid.Value = DBNull.Value;
			}

			if (Caster != null)
			{
				this.cstr.Value = Caster;
			}
			else
			{
				this.cstr.Value = DBNull.Value;
			}

			if (TagValue != null)
			{
				this.tag.Value = TagValue;
			}
			else
			{
				this.tag.Value = DBNull.Value;
			}

			tt.Value = DBNull.Value;
			ft.Value = DBNull.Value;

			AddCmd.Parameters.Clear();
			DelCmd.Parameters.Clear();
			ModCmd.Parameters.Clear();

			if (BindedCommand != null)
			{
				BindedCommand.Parameters.Add(sid);
				BindedCommand.Parameters.Add(cstr);
				BindedCommand.Parameters.Add(tag);
			}
		}

		private void ResetParameter(SqlCommand BindedCommand, string StationId, string Caster, string FromTag,string ToTag)
		{
			ResetParameter(null,StationId, Caster, null);

			if (FromTag != null)
			{
				this.ft.Value = FromTag;
			}
			else
			{
				this.ft.Value = DBNull.Value;
			}

			if (ToTag == null)
			{
				this.tt.Value = ToTag;
			}
			else
			{
				this.tt.Value = DBNull.Value;
			}

			AddCmd.Parameters.Clear();
			DelCmd.Parameters.Clear();
			ModCmd.Parameters.Clear();

			BindedCommand.Parameters.Add(sid);
			BindedCommand.Parameters.Add(cstr);
			BindedCommand.Parameters.Add(ft);
			BindedCommand.Parameters.Add(tt);
		}

		public uint MaxTagsCount
		{
			get
			{
				return MaxTagsCount_;
			}
			set
			{
				MaxTagsCount_ = value;

				using (SqlCommand cmd = con.CreateCommand())
				{
					cmd.CommandText = string.Format("UPDATE dbo.ProcedureConfigure SET Value={0} WHERE Name='MaxTagsCount';", value);
					cmd.ExecuteNonQuery();
				}
			}
		}

		#region IDisposable メンバ

		public void Dispose()
		{
			con.Close();
			con.Dispose();

			entity.Dispose();

			AddCmd.Dispose();
			DelCmd.Dispose();
			ModCmd.Dispose();
		}

		#endregion
	}
}